package com.dxp.utils;

import com.apache.tools.StrUtil;
import org.apache.commons.collections.map.CaseInsensitiveMap;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.log4j.Logger;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据库元数据解析工具类
 *
 */
public final class DatabaseUtils {

	private Logger log = Logger.getLogger(getClass());

	private String schema;
	private static DatabaseUtils obj = new DatabaseUtils();
	private Map<String,BasicDataSource> dsMap = new HashMap();
	private Map<String,String> intMark = new CaseInsensitiveMap();
	private Map<String,String> longMark = new CaseInsensitiveMap();
	private Map<String,String> doubleMark = new CaseInsensitiveMap();
	private Map<String,String> dateMark = new CaseInsensitiveMap();

	/**
	 * 禁止实例化
	 */
	private DatabaseUtils() {
		intMark.put("TINYINT","0");
		intMark.put("SMALLINT","0");
		intMark.put("MEDIUMINT","0");
		intMark.put("INT","0");
		intMark.put("INTEGER","0");
		longMark.put("BIGINT","0");
		longMark.put("LONG","0");
		doubleMark.put("NUMBER","0");
		doubleMark.put("DOUBLE","0");
		dateMark.put("DATE","0");
		dateMark.put("TIME","0");
		dateMark.put("YEAR","0");
		dateMark.put("DATETIME","0");
		dateMark.put("TIMESTAMP","0");
	}

	public static DatabaseUtils getInstance() {
		return obj;
	}

	public Connection getConnection(String sysEname,String url, String DriverClassName, String user, String password) {
		try {
			if(!dsMap.containsKey(sysEname)) {
				BasicDataSource ds = new BasicDataSource();
				ds.setDriverClassName(DriverClassName);
				ds.setUrl(url);
				ds.setUsername(user);
				ds.setPassword(password);
				ds.setMaxActive(2);
				ds.setMaxIdle(1);
				dsMap.put(sysEname,ds);
			}
			return dsMap.get(sysEname).getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
			log.warn("{}", e);
			dsMap.remove(sysEname);
		}
		return null;
	}

	public void removeDataSource(String sysEname){
		if(dsMap.containsKey(sysEname)) {
			try {
				dsMap.get(sysEname).close();
			} catch (SQLException e) {
			}
			dsMap.remove(sysEname);
		}
	}

	/**
	 * 获取数据库下所有表名
	 * @param conn
	 */
	public List<String> getTables(Connection conn,String username){
		ResultSet rs = null;
		List<String> result = new ArrayList();
		PreparedStatement stmt;
		try {
			if(StrUtil.isNotNull(username)){
				String createSql = "select table_name from all_tables a where a.OWNER = upper('"+username+"')";
				stmt = conn.prepareStatement(createSql);
				rs = stmt.executeQuery(createSql);
				while (rs.next()) {
					String name = rs.getString("table_name");
					result.add(name);
				}
			} else {
				DatabaseMetaData metaData = conn.getMetaData();
				rs = metaData.getTables(null, schema, null, new String[]{"TABLE"});
				while (rs.next()) {
					String tableName = rs.getString("TABLE_NAME");
					result.add(tableName);
				}
			}
		} catch (SQLException e) {
			log.warn("{}", e);
		} finally {
			close(conn,rs);
		}
		return result;
	}

	/**
	 * 获取数据库下所有表名+表下所字段列表
	 * @param conn
	 */
	public Map<String,List<Map<String,String>>> getAllTableNamesMap(Connection conn,String username) {
		ResultSet rs = null;
		Map<String,List<Map<String,String>>> tables = new HashMap();
		try {
			if(StrUtil.isNull(username)) {
				DatabaseMetaData metaData = conn.getMetaData();
				rs = metaData.getTables(null, schema, null, new String[]{"TABLE"});
				while (rs.next()) {
					String tableName = rs.getString("TABLE_NAME");
					List<Map<String, String>> columns = getTableColumns(conn, tableName, "");
					tables.put(tableName, columns);
				}
			}else{

			}
		} catch (SQLException e) {
			log.warn("{}", e);
		} finally {
			close(conn,rs);
		}
		return tables;
	}

	/**
	 * 获取数据库指定表下所字段列表
	 * @param conn
	 */
	public List<Map<String,String>> getTableColumns(Connection conn,String tableName,String username) {
		List<Map<String,String>> columns = new ArrayList<>();
		ResultSet rs = null;
		try {
			List<String> prmKeys = getTablePrimaryKeys(conn, tableName,username);
			if(StrUtil.isNull(username)) {
				// 获取字段基本信息
				DatabaseMetaData metaData = conn.getMetaData();
				rs = metaData.getColumns(null, schema, tableName, "%");
				while (rs.next()) {
					Map<String, String> column = new HashMap();
					String columnName = rs.getString("COLUMN_NAME");
					column.put("dataShortName", columnName.toLowerCase());
					column.put("dataAttr", toCamelNamed(columnName));
					String type = rs.getString("TYPE_NAME");
					if (intMark.containsKey(type)) {
						column.put("dataType", "1");//整型-int
					} else if (doubleMark.containsKey(type)) {
						column.put("dataType", "3");//小数-float
					} else if (longMark.containsKey(type)) {
						column.put("dataType", "2");//长整型-bigint/number
					} else if (dateMark.containsKey(type)) {
						column.put("dataType", "6");//日期-datetime/date
					} else if ("TEXT".equalsIgnoreCase(type)) {
						column.put("dataType", "7");//大字段-text
					} else if ("decimal".equalsIgnoreCase(type)) {
						column.put("dataType", "8");//精度-decimal
					} else if ("char".equalsIgnoreCase(type)) {
						column.put("dataType", "4");//字节-char
					} else {
						column.put("dataType", "5");//字符-varchar
					}
					column.put("typeName", type);
					String columnSize = rs.getString("COLUMN_SIZE");
					column.put("dataLength", columnSize);
					if ("1".equals(rs.getString("NULLABLE"))) {
						column.put("dataRestrict", "3");//可空
					} else {
						if (prmKeys.contains(columnName) && prmKeys.size() > 1) {
							column.put("dataRestrict", "4");//联合主键
						} else if (prmKeys.contains(columnName)) {
							column.put("dataRestrict", "1");//主键
						} else {
							column.put("dataRestrict", "2");//非空
						}
					}
					columns.add(column);
				}
			}else{
				String createSql = "select column_name,data_type,data_length,nullAble from all_tab_columns c where c.TABLE_NAME like '"+tableName+"'";
				PreparedStatement stmt = conn.prepareStatement(createSql);
				rs = stmt.executeQuery(createSql);
				while (rs.next()) {
					String name = rs.getString("columnName");
					String type = rs.getString("dataType");
					String dataLength = rs.getString("dataLength");
					String nullable = rs.getString("nullable");
					Map<String, String> column = new HashMap();
					column.put("dataShortName", name.toLowerCase());
					column.put("dataAttr", toCamelNamed(name));
					if (intMark.containsKey(type)) {
						column.put("dataType", "1");//整型-int
					} else if (doubleMark.containsKey(type)) {
						column.put("dataType", "3");//小数-float
					} else if (longMark.containsKey(type)) {
						column.put("dataType", "2");//长整型-bigint/number
					} else if (dateMark.containsKey(type)) {
						column.put("dataType", "6");//日期-datetime/date
					} else if ("TEXT".equalsIgnoreCase(type)) {
						column.put("dataType", "7");//大字段-text
					} else if ("decimal".equalsIgnoreCase(type)) {
						column.put("dataType", "8");//精度-decimal
					} else if ("char".equalsIgnoreCase(type)) {
						column.put("dataType", "4");//字节-char
					} else {
						column.put("dataType", "5");//字符-varchar
					}
					column.put("typeName", type);
					column.put("dataLength", dataLength);
					if ("Y".equalsIgnoreCase(nullable)) {
						column.put("dataRestrict", "3");//可空
					} else {
						if (prmKeys.contains(name) && prmKeys.size() > 1) {
							column.put("dataRestrict", "4");//联合主键
						} else if (prmKeys.contains(name)) {
							column.put("dataRestrict", "1");//主键
						} else {
							column.put("dataRestrict", "2");//非空
						}
					}
					columns.add(column);
				}
			}
		} catch (SQLException e) {
			log.warn("{}", e);
		} finally {
			close(null,rs);
		}
		return columns;
	}

	public void close(Connection con,ResultSet rs) {
		try {
			if (null != rs)
				rs.close();
		} catch (SQLException e) {
			log.error("关闭连接出错；异常信息：" + e.getMessage());
		} finally {
			try {
				if (null != con)
					con.close();
			} catch (SQLException e) {
			}
		}
	}

	private List<String> getTablePrimaryKeys(Connection conn,String tableName,String username) {
		List<String> prmKeys = new ArrayList<String>();
		ResultSet rs = null;
		try {
			if(StrUtil.isNull(username)) {
				// 获取字段基本信息
				DatabaseMetaData metaData = conn.getMetaData();
				rs = metaData.getPrimaryKeys(null, schema, tableName);
				while (rs.next()) {
					prmKeys.add(rs.getString("COLUMN_NAME"));
				}
				rs.close();
			} else {
				String createSql = "select column_name from user_cons_columns u,user_constraints c where u.table_name = '"+tableName+"' and u.constraint_name = c.index_name and c.constraint_type ='P'";
				PreparedStatement stmt = conn.prepareStatement(createSql);
				rs = stmt.executeQuery(createSql);
				while (rs.next()) {
					String COLUMN_NAME = rs.getString("column_name");
					prmKeys.add(COLUMN_NAME);
				}
				rs.close();
				stmt.close();
			}
		} catch (SQLException e) {
			log.warn("{}", e);
		}
		return prmKeys;
	}

	/**
	 * 属性值生成,符合骆驼命名
	 */
	private String toCamelNamed(String str) {
		String names[] = str.split("_");
		StringBuilder sb = new StringBuilder();
		for (int i = 0; i < names.length; i++) {
			if (i == 0) {
				sb.append(names[i].toLowerCase());
			} else {
				sb.append(names[i].substring(0, 1).toUpperCase() + names[i].substring(1).toLowerCase());
			}
		}
		return sb.toString();
	}

	/**
	 * 获取数据库类型
	 * @param driverClassName 数据库驱动
	 */
	public String getDataBaseType(String driverClassName) {
		if ("oracle.jdbc.OracleDriver".equals(driverClassName)) {
			return "oracle";
		} else if ("com.microsoft.sqlserver.jdbc.SQLServerDriver".equals(driverClassName)) {
			return "sqlserver";
		} else {
			return "mysql";
		}
	}

	public static void main(String[] args) {
		String url="jdbc:mysql://localhost:3306/test?allowMultiQueries=true&autoReconnect=true&useUnicode=true&characterEncoding=utf-8&useSSL=false";
        String user="root";
        String pwd="root";
        String dclass="com.mysql.jdbc.Driver";
		Connection con = DatabaseUtils.getInstance().getConnection("test",url,dclass,user,pwd);
		Map<String,List<Map<String,String>>> list = DatabaseUtils.getInstance().getAllTableNamesMap(con,"");
		if(null != list){
			for(String key : list.keySet()) {
				System.out.println("tableName="+key);
				List<Map<String,String>> cls = list.get(key);
				for(Map<String,String> cl:cls){
					System.out.println("cls="+cl);
				}
			}
		}
		System.out.println("====");
	}
}
